package de.risk.db;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.apache.log4j.Logger;

import com.mysql.jdbc.PreparedStatement;

import de.risk.game.CityGame;
import de.risk.game.GameConstants;
import de.risk.geo.Point;

public class CityGameSQL {
	
	private static org.apache.log4j.Logger log = Logger
			.getLogger(CityGameSQL.class);

	private DB_Connector con = null;

	public CityGameSQL() {
		con = new DB_Connector();

		con.connectDB();
		// check if the cities tables exist
		con.isTableExisting(GameConstants.TABLE_NAME_CITIES_GAME);
		con.closeConnection();
	}

	public boolean insertCityGame(CityGame city) {
		boolean inserted = false;

		con.connectDB();

		log.debug("Insert into " + GameConstants.TABLE_NAME_CITIES_GAME + " " + city.toString());

		String INSERT_RECORD = "insert into " + GameConstants.TABLE_NAME_CITIES_GAME
				+ "(cityID,besitzerID,truppen,ausbauCode) "
				+ "values(?, ?, ?, ?)";
		try {
			PreparedStatement pstmt = (PreparedStatement) DB_Connector.getCon().prepareStatement(INSERT_RECORD);
			pstmt.setInt		(1, city.getId());
			pstmt.setInt		(2, city.getBeistzerID());
			pstmt.setInt		(3, city.getTruppen());
			pstmt.setInt		(4, city.getAusbauCode());

			pstmt.executeUpdate();
			inserted = true;

		} catch (SQLException e) {
			log.error("Failed inserting city: " + city.toString());
			e.printStackTrace();
		}

		con.closeConnection();

		return inserted;
	}

	public boolean insertCities(ArrayList<CityGame> cities) {
		for (CityGame c : cities) {
			boolean success = insertCityGame(c);
			if (!success) {
				return false;
			}
		}
		return true;
	}

	public ArrayList<CityGame> getAllGameCities(){
		ArrayList<CityGame> list = new ArrayList<CityGame>();
		
		con.connectDB();

		String query =  "SELECT * "+
						"FROM risk_cities AS cit INNER JOIN risk_cities_game AS citG "+
						"ON cit.id = citG.cityID";
		log.debug("Querying GameCities");

		ResultSet rs;
		try {
			rs = DB_Connector.getStmt().executeQuery(query);
			
			while (rs.next()) {			
				int id = 		rs.getInt(1);
				String name = 	rs.getString(2);
				String bLand =	rs.getString(3);
				int einwohner = rs.getInt(4);
				Point p = new Point(rs.getDouble(5),rs.getDouble(6));
				// ignoring the cityID row 7
				int besitzerID =rs.getInt(8);
				int truppen	=	rs.getInt(9);
				int ausbauCode =rs.getInt(10);

				CityGame city = new CityGame(id, name, bLand, einwohner, p, besitzerID, truppen, ausbauCode);
				list.add(city);
			}
		} catch (SQLException e) {
			log.error("Failed querying Bundeslaender");
			e.printStackTrace();
		}

		con.closeConnection();
		
		//CityGame.printArrayList(list);		
		
		return list;
	}
	
	
	/**
	 * Does not affect the cityID, but all other attributes
	 * 
	 * @param city
	 * @return
	 */
	public boolean updateCityGame(CityGame city){
		String executeString = 
				"UPDATE risk_cities_game "+
				"SET truppen = truppen + "+city.getTruppen()+", "+
				"ausbauCode = "+city.getAusbauCode()+ " "+
				"besitzerID = "+city.getBeistzerID()+ " "+
				"WHERE besitzerID = 2";		
		log.debug("ExecuteString = "+executeString);
		try {
			DB_Connector.getStmt().execute(executeString);
		} catch (SQLException e) {
			log.error("Failed!");
			e.printStackTrace();
		}	
		return false;
	}
		
}
